package com.qingcheng.dao;

import com.qingcheng.pojo.order.CategoryReport;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

public interface CategoryReportMapper extends Mapper<CategoryReport> {



    //统计前一天的销售额
    @Select("SELECT oi.category_id1 ,oi.category_id2,oi.category_id3 , DATE_FORMAT(o.pay_time,'%Y-%m-%d') count_date,SUM(oi.num) num,SUM(oi.money) money FROM tb_order o,tb_order_item oi WHERE o.id=oi.order_id AND o.pay_status='1' AND  DATE_FORMAT(o.pay_time,'%Y-%m-%d')=#{date} GROUP BY  oi.category_id1 ,oi.category_id2,oi.category_id3 , DATE_FORMAT(o.pay_time,'%Y-%m-%d')")
    public List<CategoryReport> categoryReport(@Param("date") LocalDate date);


    //统计某段时间销售额 和 一级分类名称
    @Select("SELECT \n" +
            "\tcr.`category_id1` categoryId1,\n" +
            "\tSUM(cr.`num`) num,\n" +
            "\tSUM(cr.`money`) money,\n" +
            "\tc.`name` categoryName\n" +
            " FROM \n" +
            "\tqingcheng_order.`tb_category_report` cr ,\n" +
            "\tqingcheng_goods.`v_category`         c\n" +
            "WHERE\n" +
            "\tc.`id`=cr.`category_id1`\n" +
            "\tAND cr.`count_date`>=#{date1}\n" +
            "\tAND cr.`count_date`<=#{date2}\n" +
            "GROUP BY \n" +
            "\tcr.`category_id1`,c.`name`")
    public List<Map> categoryId1(@Param("date1") String date1, @Param("date2")String date2);

}
